# encoding: utf-8


"""

@author: tongzhenguo

@time: 2021/5/2 上午10:13

@desc:

精排
根据估值公式预估股票的合理价格
安全边际以下买入、高估卖出

1.晨星公司认为“大多数公司需要30～40%的安全边际”;
小盘成长股，行业属性倾向于周期波动型的安全边际折扣通常都要打4～6折;
医药行业的收益是比较稳定的，可以打2～3折，特别适合用这种方法推算。
2.为了避免估值有水分，一定要确保数据是稳定可预测的。
3.非经常性损益利润总额比，如果其值在10%以上的，均会对当期盈利真实性的表述带来影响
4.

FIXME 大部分股票没给出估值或者估值较低没有参考意义
"""

import numpy as np
import pandas as pd

from app.fundamentals_analytics import cagr_percent, FundamentalsAnalytics
from common import valid_check, value_rectify
from const import PROJ_HOME, TRADE_CAL_FILE, ALL_STOCK_A_FILE
from date_util import last_n_year, last_year, now_quarter, last_trade_date, now_year, get_n_day, is_trade_date
from mongo_db import MongoDB


class AppraisementCalculator(object):
    def __init__(self):
        self.mongo_db = MongoDB(uri="mongodb://127.0.0.1:27017", database="stock")
        # 年度财报时间
        self.year = int(last_year())
        self.now_year = int(now_year())
        # 季度财报时间
        self.quarter = int(now_quarter())
        self.trade_cal_df = pd.read_csv(TRADE_CAL_FILE, dtype={"cal_date": str, "is_open": str})
        self.date = last_trade_date(self.trade_cal_df)
        self.price_methods = ["peterlynch_peg", "graham_groulth_stock", "johannev_pe", "templeton_eps"]
        self.price_columns = ["彼得林奇估值", "格雷厄姆估值", "约翰聂夫估值", "邓普顿估值"]
        self.stock_all_df = pd.read_csv(ALL_STOCK_A_FILE, encoding="utf-8", dtype={"code": str})
        self.all_stock_codes = list(self.stock_all_df["code"].values)

    def pe_percent_rank(self, data):
        """可以根据历史PE分位点估值
        根据中国股市的特地，基本上在5年左右完成一个牛熊转换;
        预期每股收益=上一年度每股收益*最新季度每股收益/上年同季度每股收益
        充分低估价格=预期每股收益×滚动市盈率20%分位点
        合理价格=预期每股收益×滚动市盈率中位数
        高估价格=预期每股收益×滚动市盈率70%分位点
        相关链接：https://xueqiu.com/1414868148/151537568
        """

        def fn(row, pe_percent):
            code = row['_id']
            try:
                last_year_expect_eps = row[f"{self.year}年度-扣除非经常性损益后的每股收益(元)"]
                latest_quarter_expect_eps = row[f"{self.now_year}Q{self.quarter}季度-加权每股收益(元)"]
                last_year_same_quarter_expect_eps = row[f"{self.year}Q{self.quarter}季度-加权每股收益(元)"]
                expect_eps_ttm = last_year_expect_eps * latest_quarter_expect_eps / last_year_same_quarter_expect_eps
                return expect_eps_ttm * pe_percent
            except Exception as e:
                print(f"error code: {code} msg: {repr(e)}")

        def to_float(x):
            return float(x)

        # 季度指标不少扣非每股收益为空，先取【加权每股收益(元)】
        columns = ["_id", "2020年度-股票简称", f"{self.date}日-收盘价", f"{self.date}日-滚动市盈率",
                   f"{self.year}年度-扣除非经常性损益后的每股收益(元)",
                   f"{self.year}Q{self.quarter}季度-加权每股收益(元)",
                   f"{self.now_year}Q{self.quarter}季度-加权每股收益(元)"]
        pe_columns = []
        start_date_8 = "%s0101" % last_n_year(5, mode="str", format="YYYY")
        while start_date_8 != str(self.date):
            if start_date_8 in ["20180222", "201802223", "20180226"]:
                start_date_8 = get_n_day(start_date_8, 1)
                continue
            if is_trade_date(start_date_8, self.trade_cal_df):
                columns.append(f"{start_date_8}日-滚动市盈率")
                pe_columns.append(f"{start_date_8}日-滚动市盈率")
            start_date_8 = get_n_day(start_date_8, 1)

        ids = data["_id"].to_list()
        ids = [code__ for code__ in ids if code__[:3] != "688" and code__[0] != "3"]
        df = self.mongo_db.fetch_data("stock", {"_id": {"$in": ids}}, columns)
        df = pd.merge(data, df, how="left", on="_id")
        print(df.columns)
        columns.remove("_id")
        columns.remove("2020年度-股票简称")
        for c in columns:
            try:
                df[c] = df[c].apply(to_float)
            except Exception as e:
                print(f"exception string to float column: {c}, just drop column")
                df.drop(columns=[c], inplace=True)
                if c in pe_columns:
                    pe_columns.remove(c)
        # 计算历史分位点
        df["pe_ttm_percent_30"] = df.apply(lambda r: FundamentalsAnalytics.pe_percent(r, pe_columns, 0.3), axis=1)
        df["pe_ttm_percent_50"] = df.apply(lambda r: FundamentalsAnalytics.pe_percent(r, pe_columns, 0.5), axis=1)
        df["pe_ttm_percent_70"] = df.apply(lambda r: FundamentalsAnalytics.pe_percent(r, pe_columns, 0.7), axis=1)
        df["pe分位点估值-30%"] = df.apply(lambda r: fn(r, 0.3), axis=1)
        df["pe分位点估值"] = df.apply(lambda r: fn(r, 0.5), axis=1)
        df["pe分位点估值-70%"] = df.apply(lambda r: fn(r, 0.7), axis=1)
        if df.shape[0] == 0:
            print("市盈率分位点估值结果可能异常！！！")
            return
        print(df[["_id", "2020年度-股票简称", "pe分位点估值", "pe_ttm_percent_50",
                  f"{self.year}年度-扣除非经常性损益后的每股收益(元)",
                  f"{self.year}Q{self.quarter}季度-加权每股收益(元)",
                  f"{self.now_year}Q{self.quarter}季度-加权每股收益(元)"
                  ]])
        return df

    def peterlynch_peg(self, data):
        """ 《彼得林奇的投资笔记》彼得林奇
        市盈率÷增长率＝PEG＜1
        说明：pe取滚动市盈率PE-TTM
        G取三年净利复合增长率，G取值区间在(0.1%,50%);否则不在定义域，返回None
        净利润为为负，返回None
        0<市盈率<100,否则返回None
        0.2<PEG<1.5,否则返回None
        考虑30%安全边际，合理价格=0.7*PEG*三年净利复合增百分数/100*净利润/总股本
        """

        def fn(row, zzl, jlr, zgb, pe):
            code = row["_id"]
            if valid_check(zzl, "zzl", code) == -1 or zzl < 0.1:
                print("code %s zzl %s" % (code, zzl))
                return -1
            # 增长率过高很容易高估，最简单方法就是截断为50
            if zzl > 50:
                print("code %s zzl %s > 50 , set 50" % (code, zzl))
                zzl = 50
            if valid_check(jlr, "jlr", code) == -1:
                print("code %s jlr %s" % (code, jlr))
                return -1
            if valid_check(zgb, "zgb", code) == -1:
                print("code %s zgb %s" % (code, zgb))
                return -1
            if valid_check(pe, "pe", code) or pe < 0 or pe > 50:
                print("code %s pe %s" % (code, pe))
                return -1
            peg = pe / zzl
            if peg < 0.2:
                print(u"code %s peg %s , maybe cyclical stocks!!!" % (code, peg))
            if peg > 1.5:
                peg = 0.7 * peg
                print("code %s peg %s > 1.5 , set %s" % (code, peg / 0.7, peg))
                # return -1
            # 每股收益
            eps = jlr / zgb
            # print(pe, eps)
            return round(pe * eps, 3)

        if not is_trade_date(self.date, self.trade_cal_df):
            # 非交易日跳过
            print("date %s is not open " % self.date)
            return None
        zgb_col = "%s日-总股本" % self.date
        cur_col = "%s年度-扣除非经常性损益后的净利润(元)" % self.year
        base_col = "%s年度-扣除非经常性损益后的净利润(元)" % last_n_year(4, mode="str", format="YYYY")
        columns = ["_id", "%s日-总市值" % self.date, zgb_col, "%s日-收盘价" % self.date, cur_col, base_col,
                   "%s日-滚动市盈率" % self.date]
        ids = data["_id"].to_list()
        df = self.mongo_db.fetch_data("stock", {"_id": {"$in": ids}}, columns)
        data = pd.merge(data, df, how="left", on="_id")
        print(data.columns)
        columns.remove("_id")
        for col in columns:
            data[col] = data[col].apply(np.float32)
        if data[data["%s日-总市值" % self.date] == -1].shape[0] > 0:
            print("%s日-总市值 has null " % self.date)
            data = data[data["%s日-总市值" % self.date] != -1]
        if data[data["%s日-收盘价" % self.date] == -1].shape[0] > 0:
            print("%s日-收盘价 has null " % self.date)
            data = data[data["%s日-收盘价" % self.date] != -1]
        # 总市值的单位是万！
        data["%s日-总市值" % self.date] = data["%s日-总市值" % self.date].apply(lambda x: 10000 * x)
        data[zgb_col] = data["%s日-总市值" % self.date] / data["%s日-收盘价" % self.date]

        data["三年净利复合增长率百分数"] = data.apply(lambda x: cagr_percent(x, 3, cur_col, base_col), axis=1)
        data = data[data['三年净利复合增长率百分数'] != None]
        if data.shape[0] == 0:
            print("三年净利复合增长率百分数数据可能异常！！！")
            return
        data["彼得林奇估值"] = data.apply(
            lambda row: fn(row, row["三年净利复合增长率百分数"], row[base_col], row[zgb_col], row["%s日-滚动市盈率" % self.date]), axis=1)
        # data = data[data["彼得林奇估值"] != -1]
        if data.shape[0] == 0:
            print("彼得林奇估值结果可能异常！！！")
            return
        print(data[["_id", "%s年度-扣除非经常性损益后的净利润(元)" % self.year, "三年净利复合增长率百分数", "彼得林奇估值"]])
        return data

    def graham_groulth_stock(self, data):
        """《聪明的投资者》格雷厄姆成长股估值公式
        价值＝当前（普通）收益×（8.5+预期年增长率×2）
        当前（普通）收益是上一会计年度每股收益
        公式中的8.5，是假设增长率为0时的市盈率倍数
        预期年(收入)增长率对下一个7至10年的预测，因为一个企业由发展到壮大的最佳成长期大约就是7至10年
        预期年(收入)增长率取值区间在(0.1%,25%);否则不在定义域，返回None
        净利润/eps为为负，返回None；
        考虑30%安全边际，合理价格=0.7*eps*(8.5+2*预期年(收入)增长率)
        """

        def fn(row, eps, zzl):
            code = row["_id"]
            if valid_check(zzl, "zzl", code) == -1 or not zzl or zzl < 0.1 or zzl > 25:
                print("code %s zzl %s" % (code, zzl))
                return -1
            if zzl > 25:
                print("code %s zzl %s > 50 , set 50" % (code, zzl))
                zzl = 25
            if valid_check(eps, "eps", code) == -1 or eps < 0:
                print("code %s eps %s" % (code, eps))
                return -1
            return round(0.7 * eps * (8.5 + 2 * zzl), 3)

        eps_col = "%s年度-加权每股收益(元)" % self.year
        cur_col = "%s年度-扣除非经常性损益后的净利润(元)" % self.year
        base_col = "%s年度-扣除非经常性损益后的净利润(元)" % last_n_year(8, mode="str", format="YYYY")
        # 从mongo拉取所需数据，并确保是数值类型
        columns = ["_id", eps_col, cur_col, base_col]
        ids = data["_id"].to_list()
        df = self.mongo_db.fetch_data("stock", {"_id": {"$in": ids}}, columns)
        data = pd.merge(data[["_id"]], df, how="left", on="_id")
        columns.remove("_id")
        for col in columns:
            data[col] = data[col].apply(np.float32)
        data["七年净利复合增长率百分数"] = data.apply(lambda x: cagr_percent(x, 7, cur_col, base_col), axis=1)
        data = data[data['七年净利复合增长率百分数'] != None]
        if data.shape[0] == 0:
            print("七年净利复合增长率百分数数据可能异常！！！")
            return
        data["格雷厄姆估值"] = data.apply(lambda r: fn(r, r[eps_col], r['七年净利复合增长率百分数']), axis=1)
        data = data[data["格雷厄姆估值"] != -1]
        if data.shape[0] == 0:
            print("格雷厄姆估值结果可能异常！！！")
            return
        print(data[["_id", "格雷厄姆估值", "七年净利复合增长率百分数", eps_col]])
        return data

    def johannev_pe(self, data):
        """《约翰聂夫的成功投资》约翰聂夫
        （增长率+股息率）÷ 市盈率＝总回报率＞0.7
        盈利和收入五年增速在7%到25%之间，低于7%增速太慢了，高于25%往往要付出过高的估值。否则不在定义域，返回None
        净利润/eps为为负，返回None；
        考虑30%安全边际，合理价格=0.7*eps*(5年净利增长率百分数+股息率百分数)(如果总回报率＞0.7)
        """

        def fn(row, eps, pe, zzl, gxl):
            code = row["_id"]
            # valid_check(zzl, "zzl", code)
            # valid_check(gxl, "gxl", code)
            # valid_check(eps, "eps", code)
            # valid_check(pe, "pe", code)
            if valid_check(zzl, "zzl", code) == -1 or not zzl or zzl < 0.7 or zzl > 25:
                print("code %s zzl %s" % (code, zzl))
                return -1
            if valid_check(gxl, "gxl", code) == -1:
                print("code %s gxl %s" % (code, gxl))
                gxl = value_rectify(gxl, 0)
            if valid_check(eps, "eps", code) == -1 or not eps or eps < 0:
                print("code %s eps %s" % (code, eps))
                return -1
            if valid_check(pe, "pe", code) == -1 or not pe or pe < 0:
                print("code %s pe %s" % (code, pe))
                return -1
            roi = (zzl + gxl) / pe
            if roi <= 0.7:
                print("code %s roi %s <= 0.7" % (code, roi))
                return -1
            return round(0.7 * eps * (zzl + gxl), 2)

        if not is_trade_date(self.date, self.trade_cal_df):
            # 非交易日跳过
            print("date %s is not open " % self.date)
            return None

        # 从mongo拉取所需数据，并确保是数值类型
        eps_col = "%s年度-扣除非经常性损益后的每股收益(元)" % self.year
        pe_col = "%s日-滚动市盈率" % self.date
        cur_col = "%s年度-扣除非经常性损益后的净利润(元)" % self.year
        base_col = "%s年度-扣除非经常性损益后的净利润(元)" % last_n_year(6, mode="str", format="YYYY")
        gxl_col = "%s日-滚动股息率" % self.date
        columns = ["_id", eps_col, pe_col, cur_col, base_col, gxl_col, '%s日-收盘价' % self.date]
        ids = data["_id"].to_list()
        df = self.mongo_db.fetch_data("stock", {"_id": {"$in": ids}}, columns)
        data = pd.merge(data[["_id"]], df, how="left", on="_id")
        columns.remove("_id")
        for col in columns:
            data[col] = data[col].apply(np.float32)

        data["五年净利复合增长率百分数"] = data.apply(lambda x: cagr_percent(x, 5, cur_col, base_col), axis=1)
        data = data[data['五年净利复合增长率百分数'] != None]
        if data.shape[0] == 0:
            print("五年净利复合增长率百分数数据可能异常！！！")
            return

        data["约翰聂夫估值"] = data.apply(lambda r: fn(r, r[eps_col], r[pe_col], r['五年净利复合增长率百分数'], r[gxl_col]), axis=1)
        data = data[data["约翰聂夫估值"] != -1]
        if data.shape[0] == 0:
            print("约翰聂夫估值结果可能异常！！！")
            return
        print(data[["_id", "约翰聂夫估值", "五年净利复合增长率百分数", '%s日-收盘价' % self.date]])
        return data

    def templeton_eps(self, data):
        """《邓普顿教你逆向投资》邓普顿
        当前的股价/5年后的每股收益<5或者pe/5年收益增长率<5
        预期年(收入)增长率取值区间在(0.1%,25%);否则不在定义域，返回None
        净利润/eps为为负，返回None；
        考虑30%安全边际，合理价格=0.7*5*价格*(1+5年净利增长率百分数 / 100)
        """

        # FIXME 5年收益增长率 != 五年净利复合增长率
        def fn(row, close, zzl):
            code = row["_id"]
            if valid_check(zzl, "zzl", code) == -1 or zzl < 0.7 or zzl > 25:
                return -1
            if valid_check(close, "close", code) == -1 or close < 0:
                return -1
            return round(3.5 * close * (1 + zzl / 100), 2)

        if not is_trade_date(self.date, self.trade_cal_df):
            # 非交易日跳过
            print("date %s is not open " % self.date)
            return None

        # 从mongo拉取所需数据，并确保是数值类型
        eps_col = "%s年度-扣除非经常性损益后的每股收益(元)" % self.year
        pe_col = "%s日-滚动市盈率" % self.date
        cur_col = "%s年度-扣除非经常性损益后的净利润(元)" % self.year
        base_col = "%s年度-扣除非经常性损益后的净利润(元)" % last_n_year(6, mode="str", format="YYYY")
        gxl_col = "%s日-滚动股息率" % self.date
        close_col = "%s日-收盘价" % self.date
        columns = ["_id", eps_col, pe_col, cur_col, base_col, gxl_col, close_col]
        ids = data["_id"].to_list()
        df = self.mongo_db.fetch_data("stock", {"_id": {"$in": ids}}, columns)
        data = pd.merge(data[["_id"]], df, how="left", on="_id")
        columns.remove("_id")
        for col in columns:
            data[col] = data[col].apply(np.float32)

        data["五年净利复合增长率百分数"] = data.apply(lambda x: cagr_percent(x, 5, cur_col, base_col), axis=1)
        data = data[data['五年净利复合增长率百分数'] != None]
        if data.shape[0] == 0:
            print("五年净利复合增长率百分数数据可能异常！！！")
            return
        data["邓普顿估值"] = data.apply(lambda r: fn(r, r[close_col], r['五年净利复合增长率百分数']), axis=1)
        data = data[data["邓普顿估值"] != -1]
        if data.shape[0] == 0:
            print("邓普顿估值结果可能异常！！！")
            return
        print(data[["_id", "邓普顿估值", "五年净利复合增长率百分数", close_col]])
        return data


if __name__ == "__main__":
    ac = AppraisementCalculator()
    print("PROJ_HOME: %s" % PROJ_HOME)
    input_path = PROJ_HOME + "/data/价值白马/白马股.csv"
    input_path2 = PROJ_HOME + "/data/莫伦卡选股/莫伦卡选股.csv"
    input_path3 = PROJ_HOME + "/data/行业选股/元器件.csv"
    input_path4 = PROJ_HOME + "/data/高股息红利/高股息红利.csv"
    data = pd.read_csv(input_path3, encoding="utf-8", dtype=str)
    data["策略"] = "高股息红利"
    stock = ac.peterlynch_peg(data)
    # stock = ac.graham_groulth_stock(data)
    # stock = ac.johannev_pe(data)
    # stock = ac.templeton_eps(data)
    ac.pe_percent_rank()
